package com.example.tiantian.contact;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public final class DBHelper extends SQLiteOpenHelper {
    public static final String DATABASE_CONTACTS = "mycontacts";
    public static final String TABLE_CONTACTS = "contacts";
    public static final String WHERE_CLAUSE_ID = "_id=?";
    private static DBHelper helper;

    private SQLiteDatabase db;

    private DBHelper(Context context) {
        super(context, DATABASE_CONTACTS, null, 1);
    }

    public static DBHelper getHelper(Context context) {
        if (helper == null) {
            helper = new DBHelper(context);
        }
        return helper;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        StringBuffer tableCreate = new StringBuffer();
        tableCreate.append("create table ").append(TABLE_CONTACTS).append(" (")
                .append("_id integer primary key autoincrement,").append("name text,").append("mobilephone text,")
                .append("officephone text,").append("familyphone text,").append("address text,")
                .append("othercontact text,").append("email text,").append("position text,").append("company text,")
                .append("zipcode text,").append("remark text,").append("imageid int").append(")");
        db.execSQL(tableCreate.toString());
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Do Nothing
    }

    public long add(ContactInfo info) {
        db = getWritableDatabase();
        db.beginTransaction();
        ContentValues values = new ContentValues();
        values.put("name", info.name);
        values.put("mobilephone", info.mobilephone);
        values.put("officephone", info.officephone);
        values.put("familyphone", info.familyphone);
        values.put("address", info.address);
        values.put("othercontact", info.othercontact);
        values.put("email", info.email);
        values.put("position", info.position);
        values.put("company", info.company);
        values.put("zipcode", info.zipcode);
        values.put("remark", info.remark);
        values.put("imageid", info.imageid);
        long result = db.insert(TABLE_CONTACTS, "_id", values);
        db.setTransactionSuccessful();
        db.endTransaction();
        db.close();
        return result;
    }

    public long update(ContactInfo info) {
        db = getWritableDatabase();
        db.beginTransaction();
        ContentValues values = new ContentValues();
        values.put("name", info.name);
        values.put("mobilephone", info.mobilephone);
        values.put("officephone", info.officephone);
        values.put("familyphone", info.familyphone);
        values.put("address", info.address);
        values.put("othercontact", info.othercontact);
        values.put("email", info.email);
        values.put("position", info.position);
        values.put("company", info.company);
        values.put("zipcode", info.zipcode);
        values.put("remark", info.remark);
        values.put("imageid", info.imageid);
        long result = db.update(TABLE_CONTACTS, values, WHERE_CLAUSE_ID, new String[] { info._id + "" });
        db.setTransactionSuccessful();
        db.endTransaction();
        db.close();
        return result;
    }

    public int delete(int where) {
        db = getWritableDatabase();
        db.beginTransaction();
        int result = db.delete(TABLE_CONTACTS, WHERE_CLAUSE_ID, new String[] { where + "" });
        db.setTransactionSuccessful();
        db.endTransaction();
        db.close();
        return result;
    }

    public int delete() {
        db = getWritableDatabase();
        db.beginTransaction();
        int result = db.delete(TABLE_CONTACTS, null, null);
        db.setTransactionSuccessful();
        db.endTransaction();
        db.close();
        return result;
    }

    public List<ContactInfo> query() {
        List<ContactInfo> list = new ArrayList<ContactInfo>();
        db = getWritableDatabase();
        db.beginTransaction();
        Cursor c = db.query(TABLE_CONTACTS, null, null, null, null, null, null);
        while (c.moveToNext()) {
            ContactInfo info = new ContactInfo();
            info._id = c.getInt(c.getColumnIndex("_id"));
            info.name = c.getString(c.getColumnIndex("name"));
            info.mobilephone = c.getString(c.getColumnIndex("mobilephone"));
            info.familyphone = c.getString(c.getColumnIndex("familyphone"));
            info.officephone = c.getString(c.getColumnIndex("officephone"));
            info.address = c.getString(c.getColumnIndex("address"));
            info.company = c.getString(c.getColumnIndex("company"));
            info.position = c.getString(c.getColumnIndex("position"));
            info.zipcode = c.getString(c.getColumnIndex("zipcode"));
            info.email = c.getString(c.getColumnIndex("email"));
            info.othercontact = c.getString(c.getColumnIndex("othercontact"));
            info.remark = c.getString(c.getColumnIndex("remark"));
            info.imageid = c.getInt(c.getColumnIndex("imageid"));
            list.add(info);
        }
        db.setTransactionSuccessful();
        db.endTransaction();
        db.close();
        return list;
    }
}
